import os
import sys
import xlrd
import xlsxwriter

# 在下方输入需要拆分的文件所在文件夹位置
inputpath='input'
if getattr(sys, 'frozen', False):
    application_path = os.path.dirname(os.path.realpath(sys.executable))
elif __file__:
    application_path = os.path.dirname(__file__)
# input文件夹路径
path_parent=os.path.dirname(application_path)
path=os.path.join(path_parent,inputpath)
# 表头元素规范
listhead=[]
listlen=0
# 按分类进行的划分
splitdict = {}
# input文件读取
file_list=os.listdir(path)
file_list.sort()
# Main
file_name='';
fileNum = len(file_list)
if fileNum!=1:
    print("拆分表只能有一个")
    os.system("pause")
else:
# 输入分类选项
    splitflag = input("输入分类选项（表头元素）：")
    for file in file_list:
        file_name = os.path.join(path,file)
        workbook = xlrd.open_workbook(file_name)
# 第一张表，默认只有一张表
        sheet_1=workbook.sheet_by_index(0)
        rows=sheet_1.nrows
        clos=sheet_1.ncols
        rows_values=[sheet_1.row_values(row,0,)for row in range(rows)]
        listhead=rows_values[0]
        del rows_values[0]
# 获取拆分项在表头中的索引
        indextemp = listhead.index(splitflag)
# 遍历rows_values
        for r in rows_values:
            if r[indextemp] not in splitdict:
                splitdict[r[indextemp]]=[]
            splitdict[r[indextemp]].append(r)
# 遍历区域列表splitdict
        for (splitkey,splitvalue) in splitdict.items():
            outputpath = 'output'+splitkey+'.xlsx'
            outpath=os.path.join(path_parent,outputpath)
            work = xlsxwriter.Workbook(outpath)
            sheet = work.add_worksheet('sheet1')
            t = 0
            for listheadvalue in listhead:
                sheet.write(0,t,listhead[t])
                t+=1
            row_index = 1
            for new_row in splitvalue:
                col_index = 0
                for v in new_row:
                    sheet.write(row_index, col_index,v)
                    col_index+=1
                row_index+=1
            work.close()